﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Web.Configuration;

using System.Data;
using MySql.Data.MySqlClient;

namespace ReconUI
{
    public class DBFactory
    {
        private MySqlConnection conn = null;

        public DBFactory()
        {
            object connString = WebConfigurationManager.ConnectionStrings["mySQLConnection"];
            if (connString != null)
            {
                conn = new MySqlConnection(connString.ToString());
            }
        }

        public int AddCustomer(string customerID, string customerName, string address1, string address2, string city, string state, string country, string zipCode, string phone, string email, int isEnabled,string addedBy)
        {
            int retval = 0;
            string strQuery = "insert into mast_customer_maint(customer_id,customer_name, customer_address1, customer_address2," +
                "customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled," +
                "addby_id,addby_datetime" +
                ") values ('" + customerID + "','" + customerName + "','" + address1 + "','" + address2 + "'," +
                "'" + city + "','" + state + "','" + country + "'," +
                "'" + zipCode + "','" + phone + "','" + email + "'," +
                "" + isEnabled + ",'" + addedBy + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int RegisterUser(string userID, string userType, string remitterID, string customerID, string userName, string pwd, string roleID, string email, string mobileNumber, int isEnabled, int isLocked, int isActive, int isDomainUser,string addedBy)
        {
            int retval = 0;
            string strQuery = "insert into mast_user_maint(User_Id,User_Type,Remitter_Id,Customer_Id," +
                "User_Name,User_Password,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_LoggedIn," +
                "Is_Locked,Is_Active,Login_Attempt,LatLogin_DateTime,Password_ExpiryDate," +
                "AddBy_UserId,AddBy_Date,CurrentLogin_Datetime,Is_DomainUser) values (" +
                "'" + userID + "','" + userType + "','" + remitterID + "','" + customerID + "'," +
                "'" + userName + "','" + pwd + "','" + roleID + "','" + email + "','" + mobileNumber + "'," + isEnabled + "," + false + "," +
                "" + isLocked + "," + isActive + ",'0','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                "'" + addedBy  + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "'," + isDomainUser + ")";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int AddStatement(string customerID, string bankId, string statementDate, string postingDate, string valueDate, string tranType, string narration1, string narration2, string narration3, string narration4, string amount, string RefNoChqNo, string ReconciledAmount, string StatementStatus, int IsPartial)
        {
            int retval = 0;
            string strQuery = "insert into pro_statement(customer_id,bank_id, statement_date, posting_date,value_date," +
                "tran_type,narration1,narration2,narration3,narration4,amount,ref_no_or_cheque_no,reconciled_amount," +
                "statement_status,is_partial" +
                ") values('" + customerID + "','" + bankId  + "','" + statementDate  + "','" + postingDate  + "'," +
                "'" + valueDate  + "','" + tranType  + "','" + narration1  + "'," +
                "'" + narration2  + "','" + narration3  + "','" + narration4  + "'," +
                "'" + amount + "','" + RefNoChqNo + "','" + ReconciledAmount + "','" + StatementStatus + "'," + IsPartial  + ")";


            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int AddDiscountPenalty(string discountPenalty,string customerId ,string effectiveDate, int daySatrt, int dayEnd, double percentage, double minAmount, double maxAmount)
        {
            int retval = 0;
            int maxCode;
            string dicountPenaltyCode;

            string strQuery = "";
            //Generate dicountPenaltyCode
            //Get Max dicountPenaltyCode From table
            if (discountPenalty == "D")
            {
                strQuery = "SELECT max(discount_code) FROM mast_discount_maint m where customer_id ='" + customerId + "'";
            }
            else
            {
                strQuery = "SELECT max(panalty_code) FROM mast_panalty_maint m where customer_id ='" + customerId + "'";
            }
             try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                dicountPenaltyCode = Convert.ToString(cmd.ExecuteScalar());

            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            
            if(dicountPenaltyCode == "")
            {
                dicountPenaltyCode = discountPenalty + "0001";
            }    
            else
            {
                maxCode = Convert.ToInt32(dicountPenaltyCode.Substring(1,4));
                maxCode = maxCode + 1;
                dicountPenaltyCode = discountPenalty + Convert.ToString(maxCode).PadLeft(4,'0');
            }
           

            if (discountPenalty == "D")
            {
                strQuery = "insert into mast_discount_maint(customer_id,discount_code,effective_date,days_start,days_end,discount_percentage,min_amount,max_amount)" +
                    " values('" + customerId + "','" + dicountPenaltyCode + "','" + effectiveDate + "'," +
                    "" + daySatrt + "," + dayEnd + "," +
                    "" + percentage + "," + minAmount + "," + maxAmount + ")";
            }
            else
            {
                strQuery = "insert into mast_panalty_maint(customer_id,panalty_code,effective_date,days_start,days_end,panalty_percentage,min_amount,max_amount)" +
                    " values('" + customerId + "','" + dicountPenaltyCode + "','" + effectiveDate + "'," +
                    "" + daySatrt + "," + dayEnd + "," +
                    "" + percentage + "," + minAmount + "," + maxAmount + ")";
            }

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int AddInvoice(string remitterId, string customerID, string invoiceNumber, string invoiceDate, string currency, string amount, string RefNoChqNo,int serviceTax,int vat,int cess,int salesTax,int tax1,int tax2,int tax3,int discount, string invoiceStatus, int IsPartial)
        {
            int retval = 0;

            string strQuery = "insert into pro_invoice(remitter_id,customer_id,invoice_number, date, currency,amount," +
                "po_ref,service_tax,vat,cess,sales_tax,tax1,tax2,tax3," +
                "discount,invoic_status,is_partial" +
                ") values('" + remitterId + "','" + customerID + "','" + invoiceNumber + "','" + invoiceDate + "'," +
                "'" + currency + "','" + amount + "','" + RefNoChqNo + "'," +
                "'" + serviceTax + "','" + vat + "','" + cess + "'," +
                "'" + salesTax + "','" + tax1 + "','" + tax2 + "','" + tax3 + "','" + discount + "','" + invoiceStatus + "'," + IsPartial + ")";


            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }
                                
        public int RegisterRole(string roleCode, string roleDesc, string roleType, int isEnabled, string addedBy)
        {
            int retval = 0;
            string strQuery = "insert into mast_role_maint(Role_Id,Role_Code,Role_Desc,Role_Type,Is_Enabled," +
                "Entered_Id,Entered_Date,Access_Rights) values (" +
                "'" + roleCode  + "','" + roleCode + "','" + roleDesc  + "','" + roleType  + "'," + isEnabled + "," +               
                "'" + addedBy + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','1')";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }
        
        

        public int addRoleMenuMapping(string roleId, string menuId,int isEnabled, string addedBy)
        {
            int retval = 0;
            string strQuery = "insert into mast_role_menu_maint (role_id,menu_id,is_enabled,entered_id,entered_date)" +
                "values (" +
                "'" + roleId + "','" + menuId + "'," + isEnabled + "," +               
                "'" + addedBy + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int removeRoleMenuMapping(string roleId, string menuId)
        {
            int retval = 0;
            string strQuery = "delete from mast_role_menu_maint where role_id = '" + roleId + "' and menu_id = '" + menuId + "'";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyUser(string userID, string userType, string remitterID, string customerID, string userName,  string roleID, string email, string mobileNumber, int isEnabled, int isLocked, int isActive, int isDomainUser, string addedBy)
        {
            int retval = 0;
            string strQuery = "update mast_user_maint set User_Type  = '" + userType + "',Remitter_Id =  '" + remitterID + "'," +
                "User_Name =  '" + userName + "',Role_Id =  '" + roleID + "',Email_Id =  '" + email + "',Mobile_No =  '" + mobileNumber + "',Is_Enabled =  '" + isEnabled + "',Is_LoggedIn =  '0'," +
                "Is_Locked =  '" + isLocked + "',Is_Active =  '" + isActive + "',Login_Attempt =  '0',LatLogin_DateTime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Password_ExpiryDate =  '" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                "ModBy_UserId =  '" + addedBy + "',ModBy_Date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',CurrentLogin_Datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Is_DomainUser =  '" + isDomainUser + "' where  User_Id = '" + userID + "' and Customer_Id = '" + customerID + "'";
                

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch(Exception exc)
            {
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }
        public int setForcePwdChange(UserVO user,string tempPwd)
        {
            int retval = 0;
            /* string strQuery = "update mast_user_maint set User_Type  = '" + user.userType + "',Remitter_Id =  '" + user.remitterID + "',Customer_Id = '" + user.customerID + "'," +
                 "User_Name =  '" + user.userName + "',User_Password =  '" + user.pwd + "' ,Role_Id =  '" + user.roleId + "',Email_Id =  '" + user. + "',Mobile_No =  '" + user.mobileNumber + "',Is_Enabled =  '" + user.isEnabled + "',Is_LoggedIn =  '0'," +
                 "Is_Locked =  '" + isLocked + "',Is_Active =  '" + isActive + "',Login_Attempt =  '0',LatLogin_DateTime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Password_ExpiryDate =  '" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                 "ModBy_UserId =  '" + addedBy + "',ModBy_Date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',CurrentLogin_Datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Is_DomainUser =  '" + isDomainUser + "' where  User_Id = '" + userID + "'";
             */


           
             string strQuery = "update mast_user_maint set Is_fource_pwd_chng = '" + user.isFourcePwdChange + "',User_Password = '"+ tempPwd +"' where  User_Id = '" + user.userId+"'";
             
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }
        public int changePassword(string newPwd,string oldPwd,UserVO user)
        {
            int retval = 0;
           /* string strQuery = "update mast_user_maint set User_Type  = '" + user.userType + "',Remitter_Id =  '" + user.remitterID + "',Customer_Id = '" + user.customerID + "'," +
                "User_Name =  '" + user.userName + "',User_Password =  '" + user.pwd + "' ,Role_Id =  '" + user.roleId + "',Email_Id =  '" + user. + "',Mobile_No =  '" + user.mobileNumber + "',Is_Enabled =  '" + user.isEnabled + "',Is_LoggedIn =  '0'," +
                "Is_Locked =  '" + isLocked + "',Is_Active =  '" + isActive + "',Login_Attempt =  '0',LatLogin_DateTime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Password_ExpiryDate =  '" + System.DateTime.Now.AddDays(90).ToString("yyyy-MM-dd") + "'," +
                "ModBy_UserId =  '" + addedBy + "',ModBy_Date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',CurrentLogin_Datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',Is_DomainUser =  '" + isDomainUser + "' where  User_Id = '" + userID + "'";
            */
            string strQuery = "update mast_user_maint set User_Password ='" + newPwd + "' where  User_Id = '" + user.userId + "' AND User_Password =  '" + oldPwd + "'";
            if (user.isFourcePwdChange.Equals("Y"))
                strQuery = "update mast_user_maint set User_Password ='" + newPwd + "',Is_fource_pwd_chng = 'N' where  User_Id = '" + user.userId + "' AND User_Password =  '" + oldPwd + "'";
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyCustomer(string customerID, string customerName, string address1, string address2, string city, string state, string country, string zipCode, string phone, string email, int isEnabled, string addedBy)
        {
            int retval = 0;
            string strQuery = "update mast_customer_maint set customer_name  = '" + customerName + "',customer_address1 =  '" + address1 + "',customer_address2 = '" + address2  + "'," +
                "customer_city =  '" + city + "',customer_state =  '" + state + "' ,customer_country =  '" + country + "',customer_zipcode =  '" + zipCode + "',customer_telno =  '" + phone + "',customer_emailid =  '" + email + "',is_enabled =  '" + isEnabled +"'," +
                "modby_id =  '" + addedBy + "',modby_datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  customer_id = '" + customerID  + "'";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyRemitter(string remitterID, string remitterName, string customerID, string address1, string address2, string address3, string city, string state, string country, string zipCode, string phone, string email, int isEnabled, string addedBy, string discountCode, string penaltyCode)
        {
            int retval = 0;
            string strQuery = "update mast_remitter_maint set remitter_name  = '" + remitterName + "',address1 =  '" + address1 + "',address2 = '" + address2 + "',address3 = '" + address3 + "'," +
                "city =  '" + city + "',state =  '" + state + "' ,country =  '" + country + "',zipcode =  '" + zipCode + "',tel_no =  '" + phone + "',email_id =  '" + email + "',is_enable =  '" + isEnabled + "'," +
                "modby_id =  '" + addedBy + "',modby_datetime =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "',discount_code = '" + discountCode +"',penalty_code = '" + penaltyCode +"' where  remitter_id = '" + remitterID + "' and customer_id = '" + customerID + "'";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int ModifyRole(string roleCode, string roleDesc, string roleType, int isEnabled, string addedBy)
        {
            int retval = 0;
            string strQuery = "update mast_role_maint set Role_Id = '" + roleCode + "',Role_Code = '"+ roleCode +"', Role_Desc  = '" + roleDesc + "',Role_Type =  '" + roleType + "',Is_Enabled = '" + isEnabled + "'," +
                "modified_id =  '" + addedBy + "',modified_date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  Role_Id = '" + roleCode  + "'";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public bool ValidateUser(string uid, string pwd, string custID)
        {
            bool retval = false;
 

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT User_Id,User_Name,User_Type,Remitter_Id,Customer_Id,Role_Id,Is_fource_pwd_chng FROM mast_user_maint WHERE User_ID = ? and User_Password = ? and customer_id = ?", conn);
                cmd.Parameters.Add("uid", MySqlDbType.VarChar, 10);
                cmd.Parameters[0].Value = uid;
                cmd.Parameters.Add("pwd", MySqlDbType.VarChar, 50);
                cmd.Parameters[1].Value = pwd;
                cmd.Parameters.Add("custid", MySqlDbType.VarChar, 10);
                cmd.Parameters[2].Value = custID;
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        UserVO loggedUser = new UserVO();
                        loggedUser.userId = reader.GetString(0);
                        loggedUser.userName = reader.GetString(1);
                        loggedUser.userType = reader.GetString(2);
                        loggedUser.remitterId = reader.GetString(3);
                        loggedUser.customerId = reader.GetString(4);
                        loggedUser.roleId = reader.GetString(5);
                        loggedUser.isFourcePwdChange = reader.GetString(6);
                       // if (HttpContext.Current.Session["CurrentUserVO"] !=null)
                         
                        HttpContext.Current.Session["CurrentUserVO"] = loggedUser;
                    }
                   

                    retval = true;
                    reader.Close();
                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public string GetUserSpecificDetails(string uid)
        {
            string userName = string.Empty;

            try
            {
                conn.Open();
                //MySqlCommand cmd = new MySqlCommand("SELECT User_Name, Customer_ID FROM mast_user_maint WHERE User_ID = ? and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'", conn);
                MySqlCommand cmd = new MySqlCommand("SELECT User_Name, Customer_ID FROM mast_user_maint WHERE User_ID = ? ", conn);
                
                cmd.Parameters.Add("uid", MySqlDbType.VarChar, 10);
                cmd.Parameters[0].Value = uid;
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        userName = reader.GetString(0) + "~" + reader.GetString(1);
                    }
                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return userName;
        }
		public UserVO GetUserDetails(string uid)
        {
            string userName = string.Empty;

            try
            {
                conn.Open();
                //MySqlCommand cmd = new MySqlCommand("SELECT User_Name, Customer_ID FROM mast_user_maint WHERE User_ID = ? and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'", conn);
                MySqlCommand cmd = new MySqlCommand("SELECT User_Name, Customer_ID, Email_Id,Remitter_Id FROM mast_user_maint WHERE User_ID = ? ", conn);

                cmd.Parameters.Add("uid", MySqlDbType.VarChar, 10);
                cmd.Parameters[0].Value = uid;
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        userName = reader.GetString(0) + "~" + reader.GetString(1);
                        UserVO user         = new UserVO();
                        user.userId         = uid;
                        user.userName       = reader.GetString(0);
                        user.customerId     = reader.GetString(1);
                        user.emailId        = reader.GetString(2);
                        user.remitterId     = reader.GetString(3);
                        return user;
                    }
                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return null;
        }
        public int AddRemitter(string remitterID, string remitterName, string customerID, string address1, string address2, string address3, string city, string state, string country, string zipCode, string phone, string email, int isEnabled,string addedBy,string discountCode,string penaltyCode)
        {
            int retval = 0;
            string strQuery = "insert into mast_remitter_maint(remitter_id,remitter_name,customer_id, address1, address2,address3," +
                "city,state,country,zipcode,tel_no,email_id,is_enable," +
                "addby_id,addby_datetime,discount_code,penalty_code" +
                ") values ('" + remitterID + "','" + remitterName + "','" + customerID + "','" + address1 + "','" + address2 + "'," +
                "'" + address3 + "','" + city + "','" + state + "','" + country + "'," +
                "'" + zipCode + "','" + phone + "','" + email + "'," +
                "" + isEnabled + ",'" + addedBy + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + discountCode + "','" + penaltyCode + "')";

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strQuery, conn);
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return retval;
        }

        public int GetUnmatchedInvoiceCount(string CustomerID, int minDays, int maxDays)
        {
            int ctr = 0;

            try
            {

                string strSQL = "SELECT COUNT(*) FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND invoic_status IN ('N', 'U', 'P', 'R')";
                if (minDays != 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) between " + minDays.ToString() + " and " + maxDays.ToString() + "";
                }
                else if (minDays != 0 && maxDays == 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) >= " + minDays.ToString();
                }
                else if (minDays == 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) <= " + maxDays.ToString() + "";
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                ctr = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ctr;
        }

        public int GetUnmatchedInvoiceCountForRemitter(string CustomerID, string UserID, int minDays, int maxDays)
        {
            int ctr = 0;

            try
            {
                string strSQL = "SELECT COUNT(*) FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND Remitter_ID = (SELECT remitter_id FROM mast_user_maint WHERE user_id='" + UserID + "' and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "') AND invoic_status = 'D'";
                if (minDays != 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) between " + minDays.ToString() + " and " + maxDays.ToString() + "";
                }
                else if (minDays != 0 && maxDays == 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) >= " + minDays.ToString();
                }
                else if (minDays == 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) <= " + maxDays.ToString() + "";
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                ctr = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ctr;
        }

        public int GetUnauthorizedInvoiceCount(string CustomerID, int minDays, int maxDays)
        {
            int ctr = 0;
            try
            {
                string strSQL = "SELECT COUNT(*) FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND invoic_status IN ('M', 'C')";
                if (minDays != 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) between " + minDays.ToString() + " and " + maxDays.ToString() + "";
                }
                else if (minDays != 0 && maxDays == 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) >= " + minDays.ToString();
                }
                else if (minDays == 0 && maxDays != 0)
                {
                    strSQL += " and DATEDIFF('" + DateTime.Now.ToString("yyyy-MM-dd") + "', date) <= " + maxDays.ToString() + "";
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                ctr = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ctr;
        }

        public DataTable GetUnmatchedInvoices(string CustomerID,string RemitterId)
        {
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT invoice_unique_id, remitter_id, invoice_number, date, currency, po_ref, amount + service_tax + vat + cess + sales_tax + tax1 + tax2 + tax3 - discount as amount, invoic_status, is_partial FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND remitter_id = '" + RemitterId + "' AND invoic_status IN ('N', 'U', 'P', 'R')", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "UnmatchedInvoices");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetUnmatchedInvoicesForRemitter(string CustomerID, string UserID)
        {
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT invoice_unique_id, remitter_id, invoice_number, date, currency, po_ref, amount + service_tax + vat + cess + sales_tax + tax1 + tax2 + tax3 - discount as amount, invoic_status, is_partial FROM pro_invoice WHERE customer_id = '" + CustomerID + "' AND remitter_id = (SELECT remitter_id FROM mast_user_maint WHERE user_id='" + UserID + "' and customer_id = '" + CustomerID + "') AND invoic_status = 'D'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "UnmatchedInvoices");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetUnmatchedStatements(string CustomerID)
        {
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT statement_unique_id, bank_id, value_date, ref_no_or_cheque_no, narration1, amount FROM pro_statement WHERE customer_id = '" + CustomerID + "' AND statement_status IN ('N', 'U', 'P', 'R')", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "UnmatchedStatements");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetUnauthorizedInvoices(string CustomerID)
        {
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT mtch.invoice_unique_id, mtch.statement_unique_id, inv.invoice_number, inv.date, inv.remitter_id, inv.amount + inv.service_tax + inv.vat + inv.cess + inv.sales_tax + inv.tax1 + inv.tax2 + inv.tax3 - inv.discount as inv_amount, stmt.ref_no_or_cheque_no, stmt.value_date, stmt.narration1, stmt.amount as stmt_amount FROM pro_matching mtch, pro_invoice inv, pro_statement stmt WHERE mtch.invoice_unique_id = inv.invoice_unique_id AND mtch.statement_unique_id = stmt.statement_unique_id AND inv.customer_id = '" + CustomerID + "' AND stmt.customer_id = '" + CustomerID + "' AND invoic_status IN ('M', 'C') and statement_status IN ('M', 'C')", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "UnauthorizedInvoices");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetStatementDetailsForRemitter(string CustomerID, string PaymentReferenceNumber)
        {
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT statement_unique_id, amount as statement_amount FROM pro_statement WHERE customer_id = '" + CustomerID + "' AND ref_no_or_cheque_no = '" + PaymentReferenceNumber + "' AND statement_status IN ('N', 'U', 'P', 'R')", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "UnmatchedInvoices");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public void MatchInvoices(string custID, string userID, DataTable dtMatchList,string matchType)
        {
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                for (int x = 0; x < dtMatchList.Rows.Count; x++)
                {
                    string strQuery = "insert into pro_matching(customer_id, invoice_unique_id, statement_unique_id, invoice_amount, statement_amount, match_by, match_date, auth_by, auth_date, remitter_user, remitter_date, match_type) values (" +
                        "'" + custID + "'," + dtMatchList.Rows[x][0].ToString() + "," + dtMatchList.Rows[x][1].ToString() + "," +
                        dtMatchList.Rows[x][5].ToString() + "," + dtMatchList.Rows[x][9].ToString() + ",'" +
                        userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + matchType + "')";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='M',is_partial=false " +
                            "where customer_id='" + custID + "' and invoice_number='" + dtMatchList.Rows[x][2].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_statement set " +
                            "statement_status='M',is_partial=false " +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + dtMatchList.Rows[x][6].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        public void MatchInvoicesForRemitter(string custID, string userID, DataTable dtMatchList)
        {
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                for (int x = 0; x < dtMatchList.Rows.Count; x++)
                {
                    string strQuery = "insert into pro_matching(customer_id, invoice_unique_id, statement_unique_id, invoice_amount, statement_amount, match_by, match_date, auth_by, auth_date, remitter_user, remitter_date, match_type) values (" +
                        "'" + custID + "'," + dtMatchList.Rows[x][0].ToString() + "," + dtMatchList.Rows[x][1].ToString() + "," +
                        dtMatchList.Rows[x][4].ToString() + "," + dtMatchList.Rows[x][6].ToString() + "," +
                        "'','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + userID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','manual')";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='C',is_partial=false " +
                            "where customer_id='" + custID + "' and invoice_number='" + dtMatchList.Rows[x][2].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_statement set " +
                            "statement_status='C',is_partial=false " +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + dtMatchList.Rows[x][5].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        public void SendInvoicesToRemitter(string custID, string userID, DataTable dtInvoiceList)
        {
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                for (int x = 0; x < dtInvoiceList.Rows.Count; x++)
                {
                    string strQuery = "update pro_invoice set " +
                            "invoic_status='D',is_partial=false " +
                            "where customer_id='" + custID + "' and invoice_number='" + dtInvoiceList.Rows[x][1].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        public void AuthorizeInvoices(string custID, string userID, DataTable dtMatchList)
        {
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                for (int x = 0; x <= dtMatchList.Rows.Count; x++)
                {
                    string strQuery = "update pro_matching set " +
                        "auth_by='" + userID + "', auth_date='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                        "where Customer_ID='" + custID + "' and invoice_unique_id=" + dtMatchList.Rows[x][0].ToString() + " and statement_unique_id=" + dtMatchList.Rows[x][1].ToString();

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='A',is_partial=false " +
                            "where customer_id='" + custID + "' and invoice_number='" + dtMatchList.Rows[x][2].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_statement set " +
                            "statement_status='A',is_partial=false " +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + dtMatchList.Rows[x][6].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public void RejectInvoices(string custID, string userID, DataTable dtMatchList)
        {
            try
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                for (int x = 0; x <= dtMatchList.Rows.Count; x++)
                {
                    string strQuery = "update pro_matching set " +
                        "auth_by='" + userID + "', auth_date='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                        "where Customer_ID='" + custID + "' and invoice_unique_id=" + dtMatchList.Rows[x][0].ToString() + " and statement_unique_id=" + dtMatchList.Rows[x][1].ToString();

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_invoice set " +
                            "invoic_status='R',is_partial=false " +
                            "where customer_id='" + custID + "' and invoice_number='" + dtMatchList.Rows[x][2].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = "update pro_statement set " +
                            "statement_status='R',is_partial=false " +
                            "where customer_id='" + custID + "' and ref_no_or_cheque_no='" + dtMatchList.Rows[x][6].ToString() + "'";

                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable GetUserDetailsByUserId(string UserID)
        {
            DataSet ds = new DataSet();

            try
            {
               
                conn.Open();
                MySqlCommand cmd;

                if(string.IsNullOrEmpty(UserID))
                    cmd = new MySqlCommand("SELECT user_id,User_Type,Remitter_Id,Customer_Id,user_name,User_Password,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_Locked,Is_Active FROM mast_user_maint WHERE user_id like '" + UserID + "%'", conn);
                else
                    cmd = new MySqlCommand("SELECT user_id,User_Type,Remitter_Id,Customer_Id,user_name,User_Password,Role_Id,Email_Id,Mobile_No,Is_Enabled,Is_Locked,Is_Active FROM mast_user_maint WHERE user_id = '" + UserID + "'", conn);

                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "UserDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

       
        public DataTable GetCustomerDetailsByCustomerId(string CustomerID)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                MySqlCommand cmd;
                if (string.IsNullOrEmpty(CustomerID))
                    cmd = new MySqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled from mast_customer_maint where customer_id like '" + CustomerID + "%'", conn);
                else
                    cmd = new MySqlCommand("select  customer_id,customer_name, customer_address1, customer_address2,customer_city,customer_state, customer_country,customer_zipcode,customer_telno,customer_emailid,is_enabled from mast_customer_maint where customer_id = '" + CustomerID + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "CustomerDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }
        //SELECT role_id,role_code,role_desc,role_type,is_enabled FROM reconengine.mast_role_maint m;
        public DataTable GetRemitterDetailsByRemitterId(string RemitterId)
        {
            DataSet ds = new DataSet();

            try
            {
                
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select remitter_id,remitter_name,customer_id, address1, address2,address3,city,state,country,zipcode,tel_no,email_id,is_enable,discount_code,penalty_code from mast_remitter_maint where remitter_id = '" + RemitterId + "' and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "CustomerDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetRemitterDetails(string RemitterId,string CustomerId)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select remitter_id,remitter_name,customer_id, address1, address2,address3,city,state,country,zipcode,tel_no,email_id,is_enable from mast_remitter_maint where remitter_id like  '" + RemitterId + "%' and customer_id = '" + CustomerId + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "CustomerDetails");
            }
            catch (Exception exc)
            {

            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetDiscountDetails(string DiscountCode, string CustomerId)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                MySqlCommand cmd = new MySqlCommand("SELECT Discount_Code,Effective_Date,Days_Start,Days_End,Discount_Percentage,Min_Amount,Max_Amount FROM mast_discount_maint where Discount_Code like  '" + DiscountCode + "%' and customer_id = '" + CustomerId + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "CustomerDetails");
            }
            catch (Exception exc)
            {

            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetPenaltyDetails(string PenaltyCode, string CustomerId)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                MySqlCommand cmd = new MySqlCommand("SELECT Panalty_Code,Effective_Date,Days_Start,Days_End,Panalty_Percentage,Min_Amount,Max_Amount FROM mast_panalty_maint where Panalty_Code like  '" + PenaltyCode + "%' and customer_id = '" + CustomerId + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "CustomerDetails");
            }
            catch (Exception exc)
            {

            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }
        
        public DataTable GetRoleDetailsByRoleId(string RoleCode,int option)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                MySqlCommand cmd;
                if (option == 2)
                    cmd= new MySqlCommand("SELECT Role_Id,Role_Code,Role_Desc,Role_Type,Is_Enabled,Access_Rights FROM mast_role_maint WHERE Role_Code like '" + RoleCode + "%'", conn);
                else
                    cmd = new MySqlCommand("SELECT Role_Id,Role_Code,Role_Desc,Role_Type,Is_Enabled,Access_Rights FROM mast_role_maint WHERE Role_Code = '" + RoleCode + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "RoleDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }

        public DataTable GetMenuForRole(string RoleCode)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                MySqlCommand cmd = new MySqlCommand("SELECT role_id,menu_id FROM mast_role_menu_maint where role_id = '" + RoleCode + "'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "RoleDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }
        public DataTable GetAllRoles()
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT Role_Id,Role_Code,Role_Desc,Role_Type,Is_Enabled,Access_Rights FROM mast_role_maint", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "RoleDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }


        public DataTable GetAllMenus()
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT menu_id,menu_name FROM conf_menu_maint where menu_link <> '' and menu_link is not null", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "RoleDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }
        public DataTable GetRemitterIdForMatchingCriteria(string customerId)
        {
            DataSet ds = new DataSet();

            try
            {

                conn.Open();

                MySqlCommand cmd = new MySqlCommand("SELECT remitter_id,remitter_name FROM mast_remitter_maint where customer_id =  '" + customerId  + "' and is_active = '1' and is_enable = '1'", conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "RoleDetails");
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }
        public DataTable GetCustomer(string customerId)
        {
            DataSet ds = new DataSet();
     
            try
            {

                conn.Open();
                if (customerId.Equals(string.Empty))
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT customer_id,customer_name FROM mast_customer_maint where is_active = '1' and is_enabled = '1'", conn);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(ds, "RoleDetails");
                }
                else
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT customer_id,customer_name FROM mast_customer_maint where customer_id =  '" + customerId + "' and is_active = '1' and is_enabled = '1'", conn);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(ds, "RoleDetails");
                }

               
            }
            catch (Exception exc)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }

            return ds.Tables[0];
        }
        private void CreateTestData()
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            //delete existing data
            cmd.CommandText = "delete from mast_user_maint";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from pro_invoice";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from pro_statement";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from pro_matching";
            cmd.ExecuteNonQuery();
            //user
            cmd.CommandText = "insert into mast_user_maint(User_Id, User_Type, Remitter_Id, Customer_Id, User_Name, User_Password, Role_Id, Is_MailEnabled, Is_MobileEnabled, Email_Id, Mobile_No, Is_Enabled, Is_LoggedIn, Is_Locked, Is_Active, Login_Attempt, Is_DomainUser) values('ashpatel', 'cust', 'R03', '001', 'Ashutosh Patel', 'pwd@1234', 'MkChk', 1, 1, 'ashutosh.patel@somesite.com', '9311212345', 1, 1, 0, 1, 1, 0)";
            cmd.ExecuteNonQuery();
            //invoices
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(1, 'R03', '001', 'inv1', now(), 'INR', '2000000', 'PR1', 20000, 4000, 120, 20000, 0, 0, 0, 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(2, 'R03', '001', 'inv2', now(), 'INR', '52000', 'PR2', 5200, 140, 12, 2000, 0, 0, 0, 0, 'M', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(3, 'R03', '001', 'inv3', now(), 'INR', '13000000', 'PR1', 13200, 1300, 130, 1300, 0, 0, 0, 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(4, 'R03', '001', 'inv4', now(), 'INR', '520', 'PR3', 5100, 125, 100, 1300, 0, 0, 0, 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(5, 'R03', '001', 'inv5', now(), 'INR', '4000000', 'PR1', 40000, 7000, 220, 40000, 0, 0, 0, 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(6, 'R03', '001', 'inv6', now(), 'INR', '5000', 'PR2', 500, 150, 125, 1000, 0, 0, 0, 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(7, 'R03', '001', 'inv7', now(), 'INR', '1000000', 'PR1', 1200, 100, 10, 100, 0, 0, 0, 0, 'M', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_invoice(invoice_unique_id, remitter_id, customer_id, invoice_number, date, currency, amount, po_ref, service_tax, vat, cess, sales_tax, tax1, tax2, tax3, discount, invoic_status, is_partial) values(8, 'R03', '001', 'inv8', now(), 'INR', '7050', 'PR3', 705, 125, 100, 100, 0, 0, 0, 0, 'M', 0)";
            cmd.ExecuteNonQuery();
            //statements
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(1, '001', 'B001', now(), now(), now(), 'N', 'payment against inv1', null, null, null, 2044120, 'T00100207052013', 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(2, '001', 'B001', now(), now(), now(), 'N', 'payment against inv2', null, null, null, 52000, 'T002005407052013', 52000, 'M', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(3, '001', 'B001', now(), now(), now(), 'C', 'payment against inv7', null, null, null, 1000000, 'C312345', 1000000, 'M', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(4, '001', 'B002', now(), now(), now(), 'N', 'payment against inv8', null, null, null, 7050, 'C312345', 7050, 'M', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(5, '001', 'B002', now(), now(), now(), 'N', 'R02', null, null, null, 13000000, 'NVS31302993', 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(6, '001', 'B001', now(), now(), now(), 'N', 'from R02', null, null, null, 15930, 'NVS3030213', 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(7, '001', 'B003', now(), now(), now(), 'N', 'payment against inv4', null, null, null, 7145, 'NVCS312345', 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "insert into pro_statement(statement_unique_id, customer_id, bank_id, statement_date, posting_date, value_date, tran_type, narration1, narration2, narration3, narration4, amount, ref_no_or_cheque_no, reconciled_amount, statement_status, is_partial) values(8, '001', 'B002', now(), now(), now(), 'N', 'payment against inv8', null, null, null, 3000000, 'T29301203', 0, 'N', 0)";
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

    
}